#!/usr/bin/env python
# encoding: utf-8
# @author: liusir
# @file: excel_utils.py
# @time: 2022/11/9 8:29 PM

import os
import xlrd3

class ExcelUtils:
    def __init__(self,excel_file_path,sheet_name):
        self.excel_file_path = excel_file_path
        self.sheet_name = sheet_name
        self.sheet = self.get_sheet()  # 构造的时候，可以调用类中的普通方法
    def get_sheet(self):
        work_book = xlrd3.open_workbook( self.excel_file_path )
        sheet = work_book.sheet_by_name( self.sheet_name )
        return sheet
    def get_row_count(self):
        ''' 获取表格的行数 '''
        row_count = self.sheet.nrows
        return row_count
    def get_col_count(self):
        ''' 获取表格的列数 '''
        col_count = self.sheet.ncols
        return col_count

    def get_cell_merged_value(self,row_index, col_index):
        ''' 获取excel单元格的值（包含合并的格子） '''
        cell_value = None
        for (min_row, max_row, min_col, max_col) in self.sheet.merged_cells:
            if row_index >= min_row and row_index < max_row:
                if col_index >= min_col and col_index < max_col:
                    cell_value = self.sheet.cell_value(min_row, min_col)
                    break
                else:
                    cell_value = self.sheet.cell_value(row_index, col_index)
            else:
                cell_value = self.sheet.cell_value(row_index, col_index)
        return cell_value

    def get_all_excel_data(self):
        excel_list_data = []
        row_one = self.sheet.row_values(0)  # 获取指定行所有的内容
        for row_index in range(1, self.sheet.nrows):
            row_dict = {}
            for col_index in range(self.sheet.ncols):
                row_dict[row_one[col_index]] = self.get_cell_merged_value(row_index, col_index)
            excel_list_data.append(row_dict)
        return excel_list_data

if __name__ == "__main__":
    current_path = os.path.dirname(os.path.abspath(__file__))
    excel_file_path = os.path.join( current_path,'..','test_data','testcase_infos.xlsx' )
    excel_utils = ExcelUtils( excel_file_path,"Sheet1" )
    print( excel_utils.get_row_count() )
    print( excel_utils.get_col_count() )
    print( excel_utils.get_cell_merged_value(4,2) )
    print("******************************")
    print( excel_utils.get_all_excel_data() )

